﻿/*
create database fb_quartz default character set utf8 collate utf8_general_ci;
create user 'qhfwbz'@'%' identified by 'qhfwbzdb312';
create user 'qhfwbz'@'localhost' identified by 'qhfwbzdb312';
grant select,insert,update,delete,create on fb_quartz.* to qhfwbz;
flush privileges;
*/

DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ_LOCKS;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_SIMPROP_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ_CALENDARS;


CREATE TABLE QRTZ_JOB_DETAILS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    DESCRIPTION VARCHAR(250) NULL,
    JOB_CLASS_NAME   VARCHAR(250) NOT NULL,
    IS_DURABLE VARCHAR(1) NOT NULL,
    IS_NONCONCURRENT VARCHAR(1) NOT NULL,
    IS_UPDATE_DATA VARCHAR(1) NOT NULL,
    REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
    JOB_DATA BLOB NULL,
    PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    DESCRIPTION VARCHAR(250) NULL,
    NEXT_FIRE_TIME BIGINT(13) NULL,
    PREV_FIRE_TIME BIGINT(13) NULL,
    PRIORITY INTEGER NULL,
    TRIGGER_STATE VARCHAR(16) NOT NULL,
    TRIGGER_TYPE VARCHAR(8) NOT NULL,
    START_TIME BIGINT(13) NOT NULL,
    END_TIME BIGINT(13) NULL,
    CALENDAR_NAME VARCHAR(200) NULL,
    MISFIRE_INSTR SMALLINT(2) NULL,
    JOB_DATA BLOB NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
        REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ_SIMPLE_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    REPEAT_COUNT BIGINT(7) NOT NULL,
    REPEAT_INTERVAL BIGINT(12) NOT NULL,
    TIMES_TRIGGERED BIGINT(10) NOT NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_CRON_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    CRON_EXPRESSION VARCHAR(200) NOT NULL,
    TIME_ZONE_ID VARCHAR(80),
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_SIMPROP_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    STR_PROP_1 VARCHAR(512) NULL,
    STR_PROP_2 VARCHAR(512) NULL,
    STR_PROP_3 VARCHAR(512) NULL,
    INT_PROP_1 INT NULL,
    INT_PROP_2 INT NULL,
    LONG_PROP_1 BIGINT NULL,
    LONG_PROP_2 BIGINT NULL,
    DEC_PROP_1 NUMERIC(13,4) NULL,
    DEC_PROP_2 NUMERIC(13,4) NULL,
    BOOL_PROP_1 VARCHAR(1) NULL,
    BOOL_PROP_2 VARCHAR(1) NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
    REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_BLOB_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    BLOB_DATA BLOB NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
    REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_CALENDARS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    CALENDAR_NAME  VARCHAR(200) NOT NULL,
    CALENDAR BLOB NOT NULL,
    PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
);

CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_GROUP  VARCHAR(200) NOT NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_FIRED_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    ENTRY_ID VARCHAR(95) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    INSTANCE_NAME VARCHAR(200) NOT NULL,
    FIRED_TIME BIGINT(13) NOT NULL,
    SCHED_TIME BIGINT(13) NOT NULL,
    PRIORITY INTEGER NOT NULL,
    STATE VARCHAR(16) NOT NULL,
    JOB_NAME VARCHAR(200) NULL,
    JOB_GROUP VARCHAR(200) NULL,
    IS_NONCONCURRENT VARCHAR(1) NULL,
    REQUESTS_RECOVERY VARCHAR(1) NULL,
    PRIMARY KEY (SCHED_NAME,ENTRY_ID)
);

CREATE TABLE QRTZ_SCHEDULER_STATE
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    INSTANCE_NAME VARCHAR(200) NOT NULL,
    LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
    CHECKIN_INTERVAL BIGINT(13) NOT NULL,
    PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
);

CREATE TABLE QRTZ_LOCKS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    LOCK_NAME  VARCHAR(40) NOT NULL,
    PRIMARY KEY (SCHED_NAME,LOCK_NAME)
);


commit;

DROP TABLE IF EXISTS QRTZ_JOB;
DROP TABLE IF EXISTS QRTZ_JOB_log;

-- ----------------------------
-- Table structure for QRTZ_JOB
-- ----------------------------
DROP TABLE IF EXISTS `QRTZ_JOB`;
CREATE TABLE `QRTZ_JOB`  (
                             `JOB_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '任务id',
                             `BEAN_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'spring bean名称',
                             `METHOD_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '方法名',
                             `PARAMS` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '参数',
                             `CRON_EXPRESSION` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'cron表达式',
                             `START_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始执行时间',
                             `END_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '结束执行时间',
                             `STATUS` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任务状态  0：正常  1：暂停 2：删除',
                             `UPDATE_STATUS` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '加载状态  0：未加载  1：已加载',
                             `REMARK` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',
                             `CREATE_TIME` datetime(0) DEFAULT NULL COMMENT '创建时间',
                             `TRANS_TYPE` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '转换类型  0：普通无转换定时任务，1：A查询-B操作 普通 2：B操作 3：A查询-B操作 4: A查询-B查询-C操作 5: A查询-B操作-C操作',
                             `TEMPLATE_SRC_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '数据源 A',
                             `TEMPLATE_DST_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '目标源 B',
                             `TEMPLATE_DST_TWO_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '目标数据源2 C',
                             `SRC_COMMD` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '源脚本',
                             `DST_COMMD` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '目的脚本',
                             `DST_TWO_COMMD` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '目的脚本2',
                             PRIMARY KEY (`JOB_ID`) USING BTREE,
                             INDEX `t_job_create_time`(`CREATE_TIME`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '定时任务表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of QRTZ_JOB
-- ----------------------------
INSERT INTO `qrtz_job` VALUES (1, 'loadUpdateTask', 'loadUpdateJob', NULL, '0/30 * * * * ?', '2018-03-20 21:55:14', '3000-01-01 00:00:00', '0', '0', '更新 task自动读取', '2020-07-12 21:31:10', '0', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `qrtz_job` VALUES (2, 'testTask', 'test', 'mrbird', '0,15,25,45,55 * * * * ?', '2020-10-28 12:57:00', '2020-10-28 12:59:00', '0', '1', '有参任务调度测试~~', '2020-10-28 21:31:10', '0', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `qrtz_job` VALUES (3, 'testTask', 'test1', NULL, '0/30 * * * * ?', '2020-10-28 13:00:00', '3000-01-01 00:00:00', '0', '1', '无参任务调度测试', '2020-07-12 21:31:10', '0', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `qrtz_job` VALUES (4, 'testTask', 'test', 'hello world', '5-8 20-40 16-20 6,9,12,13,14,15,20,21,22,23,24,25,26,27,28,29 6 ?', '2020-06-20 21:55:14', '3000-01-01 00:00:00', '0', '1', '有参任务调度测试,每隔一秒触发', '2020-07-12 21:31:10', '0', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `qrtz_job` VALUES (5, 'testTask', 'test2', NULL, '0/5 * * * * ?', '2020-06-20 21:55:14', '3000-01-01 00:00:00', '2', '1', '测试异常', '2020-07-12 21:31:10', '0', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `qrtz_job` VALUES (6, 'sendData', 'transData', '', '0 23 0 1 * ?', '2020-11-03 00:01:00', '3000-01-01 00:00:00', '0', '1', '总计指标每月1号凌晨清零', '2020-11-02 10:22:47', '2', '', 'jdbcTemplateKdpt', NULL, '', 'update total_index_td t set t.date = date_format(now(), \'%Y-%m\'), t.update_time = now(), t.num = 0', NULL);
INSERT INTO `qrtz_job` VALUES (7, 'sendData', 'transData', '', '0 0/2 * * * ?', '2020-11-03 04:30:00', '3000-01-01 00:00:00', '0', '1', '非609拦截重复工单打标', '2020-11-02 10:22:47', '3', 'jdbcTemplateKdpt', 'jdbcTemplateKdpt', NULL, 'select (\r\nselect count(1) from hisentry_sheet_td p where   p.bespeak_count = 1 and p.huidandepart = \'派单\' and p.fault_no = t.fault_no\r\nand p.busi_id = t.busi_id and p.occur_time < t.occur_time and p.occur_time > date_add(t.occur_time, interval -30 day)\r\n) timescfn609,t.sheet_no from hisentry_sheet_td t where  t.timescfn609 = -1 and t.bespeak_count = 1 and t.huidandepart = \'派单\' and t.arch_time > date_format(date_add(now(), interval -1 day), \'%Y-%m-%d 00:00:00\')\r\nand t.arch_time < date_format(date_add(now(), interval -1 day), \'%Y-%m-%d 23:59:59\') limit 0,5', 'update hisentry_sheet_td t set t.timescfn609 = ? where t.sheet_no = ?', NULL);
INSERT INTO `qrtz_job` VALUES (8, 'sendData', 'transData', '', '0 30 5 * * ?', '2020-11-03 04:30:00', '3000-01-01 00:00:00', '0', '1', '网格障碍回单数当月总计 1', '2020-11-02 10:22:47', '3', 'jdbcTemplateKdpt', 'jdbcTemplateKdpt', NULL, 'select count(1) num,t.huidanper_orgid orgid,1 type  from hisentry_sheet_td t where t.bespeak_count = 1 and t.huidandepart = \'派单\' and t.arch_time > date_format(date_add(now(), interval -1 day), \'%Y-%m-%d 00:00:00\')\r\nand t.arch_time < date_format(date_add(now(), interval -1 day), \'%Y-%m-%d 23:59:59\') and t.huidanper_orgid is not null  group by t.huidanper_orgid having count(1) > 0', 'update total_index_td t set t.num = t.num+? , t.update_time = now() where t.orgid = ?  and t.type = ?', NULL);
INSERT INTO `qrtz_job` VALUES (9, 'sendData', 'transData', '', '0 30 5 * * ?', '2020-11-03 04:30:00', '3000-01-01 00:00:00', '0', '1', '网格障碍回单数总计记录 1', '2020-11-02 10:22:47', '3', 'jdbcTemplateKdpt', 'jdbcTemplateKdpt', NULL, 'select count(1) num,t.huidanper_orgid orgid,1 type  from hisentry_sheet_td t where t.bespeak_count = 1 and t.huidandepart = \'派单\' and t.arch_time > date_format(date_add(now(), interval -1 day), \'%Y-%m-%d 00:00:00\')\r\nand t.arch_time < date_format(date_add(now(), interval -1 day), \'%Y-%m-%d 23:59:59\') and t.huidanper_orgid is not null  group by t.huidanper_orgid having count(1) > 0', 'insert total_everyday_index_td (date,num,orgid,type,insert_time) value (date_format(date_add(now(), interval -1 day), \'%Y-%m-%d\'),?,?,?,now())', NULL);
INSERT INTO `qrtz_job` VALUES (10, 'sendData', 'transData', NULL, '0 0 1 * * ?', '2020-11-06 19:50:00', '3000-01-01 00:00:00', '0', '1', '定时服务日志自动清理', '2020-10-19 16:19:21', '2', '', 'jdbcTemplateQuartz', NULL, '', 'delete  t.*   from qrtz_job_log  t  where   t.CREATE_TIME  <  date_add(now(), interval -30 DAY )', NULL);
INSERT INTO `qrtz_job` VALUES (11, 'sendData', 'transData', NULL, '0/30 * * * * ?', '2020-11-06 19:50:00', '3000-01-01 00:00:00', '1', '1', '短信发送', '2020-12-06 16:19:21', '5', 'jdbcTemplateKdpt', 'jdbcTemplateDdProd', 'jdbcTemplateKdpt', 'select  t.send_num,t.notice_content,t.notice_record_id  from SIM_NOTICE_RECORD  t where t.send_state = 0', 'INSERT INTO SHORT_MSG_SEND(SM_ID,PHONE_NBR,CONTENT,STATUS, SEND_TYPE, CREATE_DT, PROCESSINSTID, MSG_START_TIME, MSG_END_TIME,MSG_SEND_DATE)\r\n          VALUES(SEQ_SHORT_MSG_SEND.NEXTVAL,?,?, 0,1, SYSDATE,TO_NUMBER(\'-\' || ?),\'00:00\',\'23:59\',SYSDATE)', 'update SIM_NOTICE_RECORD  t set send_state = 1,send_result=0,send_result_desc=CONCAT(\'成功-> receivePhone:\', ?, \' msg:\', ?)   where notice_record_id = ?');
INSERT INTO `qrtz_job` VALUES (12, 'sendData', 'transData', NULL, '0 0 9 * * ?', '2020-11-06 19:50:00', '3000-01-01 00:00:00', '0', '1', '9点地市网格在途工单统计', '2020-12-06 16:19:21', '4', 'jdbcTemplateKdpt', 'jdbcTemplateKdpt', 'jdbcTemplateKdpt', 'select q.dic_name cd1,q.started started1,q.ended ended1,q.dic_name cd2,q.started started2,q.ended ended2 from (\r\nselect \r\n(case when LENGTH(cd) >= LOCATE(\',\',cd) then   case when INSTR(cd,\',\') = 1 then 0 else  left(cd,LOCATE(\',\',cd)-1)  end   else  \'0\'  end)*60 started,\r\n(case when LENGTH(cd) = LOCATE(\',\',cd) then 30000 else   case when INSTR(cd,\',\') = 1 then right(cd,2) else right(cd,LOCATE(\',\',cd)-1) end  end)*60 ended,\r\np.* from (\r\nselect d.DIC_ID,d.DIC_NAME,TRIM(RTRIM(d.DIC_CODE)) cd  from sys_dic  d where  d.BUSI_SCENE_CODE = \'CHAOSHI_PERIOD\' and d.STATUS = 1 and LENGTH(TRIM(RTRIM(d.DIC_CODE))) > 0 and LOCATE(\',\',d.DIC_CODE) > 0\r\n)  p ) q\r\n', 'select s.city_code, \'\' city_name,\r\n			w.fgsorgid dis_code,w.fgsorgname dis_name,\r\n			w.zjorgid branch_code,w.zjorgname branch_name,\r\n			s.grid_code,w.orgname grid_name,\r\n			9 chaoshi_type,1 order_type,\r\n			? sta_period,s.sta_count,now() insert_time\r\nfrom (\r\nSELECT\r\n	t.orgid city_code,\r\n	p.orgid grid_code,\r\n	count(1) sta_count\r\nFROM\r\n	entry_sheet_td t\r\n	LEFT JOIN entry_pianquzb_report p ON p.orgid = t.fbzjorgid \r\nWHERE\r\n	t.sheet_state = 0 \r\nAND t.bespeak_count = 1\r\nAND TIMESTAMPDIFF(MINUTE, t.occur_time, date_format(now(), \'%Y-%m-%d 14:00:00\')) >= ?\r\nAND TIMESTAMPDIFF(MINUTE, t.occur_time, date_format(now(), \'%Y-%m-%d 14:00:00\')) < ?\r\nGROUP BY t.orgid,p.orgid) s left join entry_pianquzb_report w on s.grid_code = w.orgid\r\n\r\nunion\r\n\r\nselect s.city_code, \'\' city_name,\r\n			w.fgsorgid dis_code,w.fgsorgname dis_name,\r\n			w.zjorgid branch_code,w.zjorgname branch_name,\r\n			s.grid_code,w.orgname grid_name,\r\n			9 chaoshi_type,0 order_type,\r\n			? sta_period,s.sta_count,now() insert_time\r\nfrom (\r\nselect  t.latn city_code,\r\n				p.orgid grid_code,\r\n				count(distinct t.cust_order_cd) sta_count\r\nfrom wm_sheet_dealinfo  t left join entry_pianquzb_report p  on p.orgid = t.fbzjorgid\r\nwhere t.last_flag = 0 and t.optname = \'装\' and t.explorateflag = \'否\'\r\nand TIMESTAMPDIFF(MINUTE, t.recevier_dt, date_format(now(), \'%Y-%m-%d 14:00:00\')) >= ?\r\nand TIMESTAMPDIFF(MINUTE, t.recevier_dt, date_format(now(), \'%Y-%m-%d 14:00:00\')) < ?\r\ngroup by  t.latn,p.orgid) s left join entry_pianquzb_report w on s.grid_code = w.orgid\r\n', 'insert csp_chaoshi_count1 (city_code,city_name,dis_code,dis_name,branch_code,branch_name,grid_code,grid_name,chaoshi_type,order_type,sta_period,sta_count,insert_time) value (?,?,?,?,?,?,?,?,?,?,?,?,?)');
INSERT INTO `qrtz_job` VALUES (13, 'sendData', 'transData', NULL, '0 0 14 * * ?', '2020-11-06 19:50:00', '3000-01-01 00:00:00', '0', '1', '14点地市网格在途工单统计', '2020-12-06 16:19:21', '4', 'jdbcTemplateKdpt', 'jdbcTemplateKdpt', 'jdbcTemplateKdpt', 'select q.dic_name cd1,q.started started1,q.ended ended1,q.dic_name cd2,q.started started2,q.ended ended2 from (\r\nselect \r\n(case when LENGTH(cd) >= LOCATE(\',\',cd) then   case when INSTR(cd,\',\') = 1 then 0 else  left(cd,LOCATE(\',\',cd)-1)  end   else  \'0\'  end)*60 started,\r\n(case when LENGTH(cd) = LOCATE(\',\',cd) then 30000 else   case when INSTR(cd,\',\') = 1 then right(cd,2) else right(cd,LOCATE(\',\',cd)-1) end  end)*60 ended,\r\np.* from (\r\nselect d.DIC_ID,d.DIC_NAME,TRIM(RTRIM(d.DIC_CODE)) cd  from sys_dic  d where  d.BUSI_SCENE_CODE = \'CHAOSHI_PERIOD\' and d.STATUS = 1 and LENGTH(TRIM(RTRIM(d.DIC_CODE))) > 0 and LOCATE(\',\',d.DIC_CODE) > 0\r\n)  p ) q\r\n', 'select s.city_code, \'\' city_name,\r\n			w.fgsorgid dis_code,w.fgsorgname dis_name,\r\n			w.zjorgid branch_code,w.zjorgname branch_name,\r\n			s.grid_code,w.orgname grid_name,\r\n			14 chaoshi_type,1 order_type,\r\n			? sta_period,s.sta_count,now() insert_time\r\nfrom (\r\nSELECT\r\n	t.orgid city_code,\r\n	p.orgid grid_code,\r\n	count(1) sta_count\r\nFROM\r\n	entry_sheet_td t\r\n	LEFT JOIN entry_pianquzb_report p ON p.orgid = t.fbzjorgid \r\nWHERE\r\n	t.sheet_state = 0 \r\nAND t.bespeak_count = 1\r\nAND TIMESTAMPDIFF(MINUTE, t.occur_time, date_format(now(), \'%Y-%m-%d 14:00:00\')) >= ?\r\nAND TIMESTAMPDIFF(MINUTE, t.occur_time, date_format(now(), \'%Y-%m-%d 14:00:00\')) < ?\r\nGROUP BY t.orgid,p.orgid) s left join entry_pianquzb_report w on s.grid_code = w.orgid\r\n\r\nunion\r\n\r\nselect s.city_code, \'\' city_name,\r\n			w.fgsorgid dis_code,w.fgsorgname dis_name,\r\n			w.zjorgid branch_code,w.zjorgname branch_name,\r\n			s.grid_code,w.orgname grid_name,\r\n			14 chaoshi_type,0 order_type,\r\n			? sta_period,s.sta_count,now() insert_time\r\nfrom (\r\nselect  t.latn city_code,\r\n				p.orgid grid_code,\r\n				count(distinct t.cust_order_cd) sta_count\r\nfrom wm_sheet_dealinfo  t left join entry_pianquzb_report p  on p.orgid = t.fbzjorgid\r\nwhere t.last_flag = 0 and t.optname = \'装\' and t.explorateflag = \'否\'\r\nand TIMESTAMPDIFF(MINUTE, t.recevier_dt, date_format(now(), \'%Y-%m-%d 14:00:00\')) >= ?\r\nand TIMESTAMPDIFF(MINUTE, t.recevier_dt, date_format(now(), \'%Y-%m-%d 14:00:00\')) < ?\r\ngroup by  t.latn,p.orgid) s left join entry_pianquzb_report w on s.grid_code = w.orgid\r\n', 'insert csp_chaoshi_count1 (city_code,city_name,dis_code,dis_name,branch_code,branch_name,grid_code,grid_name,chaoshi_type,order_type,sta_period,sta_count,insert_time) value (?,?,?,?,?,?,?,?,?,?,?,?,?)');
INSERT INTO `qrtz_job` VALUES (14, 'sendData', 'transData', '', '0 25 0 * * ?', '2020-11-03 00:01:00', '3000-01-01 00:00:00', '0', '1', 'sys_org1数据清除', '2020-11-02 10:22:47', '2', '', 'jdbcTemplateKdpt', NULL, '', 'truncate sys_org1', NULL);
INSERT INTO `qrtz_job` VALUES (15, 'sendData', 'transData', '', '0 30 0 * * ?', '2020-11-03 00:01:00', '3000-01-01 00:00:00', '0', '1', 'sys_org1数据同步', '2020-11-02 10:22:47', '3', 'jdbcTemplateFbProd', 'jdbcTemplateKdpt', NULL, 'SELECT\r\n	O.ORGID ORG_ID,\r\n	O.ORGNAME ORG_NAME,\r\n	O.PARENTORGID PARENT_ORG_ID,\r\n	O.ORGSEQ ORG_SEQ,\r\n	O.ORGTYPEID ORG_TYPE_ID,\r\n	O.ORGLEVEL ORG_GRADE,\r\n	O.LINKMAN ORG_CONTACTER,\r\n	O.LINKMANTEL CONTACT_TEL,\r\n	O.ORGADDRESS ORG_ADDRESS,\r\n	(CASE WHEN (SELECT COUNT(1) FROM EOSORG_T_ORGANIZATION@Fwbznew.regress.rdbms.dev.us.oracle.com P WHERE  P.PARENTORGID = O.ORGID) > 0 THEN 0 ELSE 1 END) IS_LEAF,\r\n	O.SHOWORDER DISPLAY_ORDER,\r\n	O.MEMO MEMO,\r\n	DECODE( O.DELFLAG, 0, 1, 0 ) STATUS \r\nFROM\r\n	EOSORG_T_ORGANIZATION@Fwbznew.regress.rdbms.dev.us.oracle.com O', 'insert sys_org1 (org_id,org_name,parent_org_id,org_seq,org_type_id,org_grade,org_contacter,contact_tel,org_address,is_leaf,display_order,memo,status) value (?,?,?,?,?,?,?,?,?,?,?,?,?)', NULL);

-- ----------------------------
-- Table structure for t_job_log
-- ----------------------------
CREATE TABLE `QRTZ_JOB_LOG`  (
                              `LOG_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '任务日志id',
                              `JOB_ID` bigint(20) NOT NULL COMMENT '任务id',
                              `BEAN_NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'spring bean名称',
                              `METHOD_NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '方法名',
                              `PARAMS` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '参数',
                              `STATUS` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '任务状态    0：成功    1：失败',
                              `ERROR` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '失败信息',
                              `TIMES` decimal(11, 0) NULL DEFAULT NULL COMMENT '耗时(单位：毫秒)',
                              `CREATE_TIME` datetime(0) DEFAULT NOW() COMMENT '创建时间',
                              PRIMARY KEY (`LOG_ID`),
                              KEY `QRTZ_JOB_LOG_create_time` (`CREATE_TIME`)
) ENGINE = MyISAM AUTO_INCREMENT = 0 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '调度日志表' ROW_FORMAT = Dynamic;
